CREATE PROCEDURE  sp_asi_AddressUpdate
as
declare
@addrnum int,
@ADDRESS_1 varchar(40),
@ADDRESS_2 varchar(40),
@ADDRESS_3 varchar(40),
@CITY varchar(40),
@STATE_PROVINCE varchar(15),
@ZIP varchar(10),
@COUNTRY varchar(25),
@code int,
@full_address varchar(255),
@formula varchar(255),
@addrlen integer,
@ID varchar(10),
@Preferred_Mail integer
set nocount on
DECLARE Get_Addrs cursor for
select ID,PREFERRED_MAIL,ADDRESS_NUM,ADDRESS_1,ADDRESS_2,ADDRESS_3,CITY,STATE_PROVINCE,ZIP,COUNTRY from Name_Address
where datalength(ADDRESS_1)>1
open Get_Addrs
fetch next from Get_Addrs into @ID,@Preferred_Mail,@addrnum,@ADDRESS_1,@ADDRESS_2,@ADDRESS_3,@CITY,@STATE_PROVINCE,@ZIP,@COUNTRY
While @@FETCH_STATUS<>-1
BEGIN
if  @@FETCH_STATUS <> -2
BEGIN
select @code=NULL
select @code=ADDRESS_FORMAT from Country_Names  where COUNTRY=@COUNTRY
select @code=isnull(@code,0)
if @code=0  
begin  
if (not exists (select * from Country_Addr_Layouts where ADDRESS_FORMAT=0))
insert into Country_Addr_Layouts (ADDRESS_FORMAT, ADDRESS_LAYOUT, FORMULA)
values (0, 'ADDRESS_1[N]ADDRESS_2[N]ADDRESS_3[N]CITY[C][S]STATE_PROVINCE[S]ZIP[N][U]COUNTRY',
'ADDRESS_1[N]ADDRESS_2[N]CITY[C][S]STATE_PROVINCE[S]ZIP[N][U]COUNTRY')
end
begin
select @formula=FORMULA from Country_Addr_Layouts where ADDRESS_FORMAT=@code
while charindex('[N]',@formula)>0
begin
select @formula=stuff(@formula,charindex('[N]',@formula),3,char(13))
end
while charindex('[S]',@formula)>0
begin
select @formula=stuff(@formula,charindex('[S]',@formula),3,' ')
end
while charindex('[C]',@formula)>0
begin
select @formula=stuff(@formula,charindex('[C]',@formula),3,',')
end
if charindex('CITY',@formula)>0
select @formula=stuff(@formula,charindex('CITY',@formula),4,'_CITY')
if charindex('ZIP',@formula)>0
select @formula=stuff(@formula,charindex('ZIP',@formula),3,'_ZIP')
if charindex('COUNTRY',@formula)>0
select @formula=stuff(@formula,charindex('COUNTRY',@formula),7,'_COUNTRY')
if charindex('[U]ADDRESS_1',@formula)>0
select @formula=stuff(@formula,charindex('[U]ADDRESS_1',@formula),12,upper(@ADDRESS_1))
if charindex('[U]ADDRESS_2',@formula)>0
select @formula=stuff(@formula,charindex('[U]ADDRESS_2',@formula),12,upper(@ADDRESS_2))
if charindex('[U]ADDRESS_3',@formula)>0
select @formula=stuff(@formula,charindex('[U]ADDRESS_3',@formula),12,upper(@ADDRESS_3))
if charindex('[U]_CITY',@formula)>0
   begin
   if charindex('[U]_CITY,',@formula)>0
     begin
     if datalength (@STATE_PROVINCE) = 0  
       select @formula=stuff(@formula,charindex('[U]_CITY,',@formula),9,'[U]_CITY')
     end
   select @formula=stuff(@formula,charindex('[U]_CITY',@formula),8,upper(@CITY))
   end
if charindex('[U]STATE_PROVINCE',@formula)>0
select @formula=stuff(@formula,charindex('[U]STATE_PROVINCE',@formula),17,upper(@STATE_PROVINCE))
if charindex('[U]_ZIP',@formula)>0
select @formula=stuff(@formula,charindex('[U]_ZIP',@formula),7,upper(@ZIP))
if charindex('[U]_COUNTRY',@formula)>0
select @formula=stuff(@formula,charindex('[U]_COUNTRY',@formula),11,upper(@COUNTRY))
if charindex('ADDRESS_1',@formula)>0
   select @formula=stuff(@formula,charindex('ADDRESS_1',@formula),9,@ADDRESS_1)
if charindex('ADDRESS_2',@formula)>0
   select @formula=stuff(@formula,charindex('ADDRESS_2',@formula),9,@ADDRESS_2)
if charindex('ADDRESS_3',@formula)>0
   select @formula=stuff(@formula,charindex('ADDRESS_3',@formula),9,@ADDRESS_3)
if charindex('_CITY',@formula)>0
   begin
   if charindex('_CITY,',@formula)>0
     begin
     if datalength (@STATE_PROVINCE) = 0  
       select @formula=stuff(@formula,charindex('_CITY,',@formula),6,'_CITY')
     end
   select @formula=stuff(@formula,charindex('_CITY',@formula),5,@CITY)
   end
if charindex('STATE_PROVINCE',@formula)>0
select @formula=stuff(@formula,charindex('STATE_PROVINCE',@formula),14,@STATE_PROVINCE)
if charindex('_ZIP',@formula)>0
select @formula=stuff(@formula,charindex('_ZIP',@formula),4,@ZIP)
if charindex('_COUNTRY',@formula)>0
select @formula=stuff(@formula,charindex('_COUNTRY',@formula),8,@COUNTRY)
if charindex('US1',@formula)>0
select @formula=stuff(@formula,charindex('US1',@formula),3,'')
if charindex('US2',@formula)>0
select @formula=stuff(@formula,charindex('US2',@formula),3,'')
while charindex(char(13)+' ',@formula)>0      
begin        
select @formula=stuff(@formula,charindex(char(13)+' ',@formula),2,char(13))      
end
while charindex(char(13)+',',@formula)>0      
begin        
select @formula=stuff(@formula,charindex(char(13)+',',@formula),2,char(13))      
end
while charindex(char(13)+char(13),@formula)>0      
begin        
select @formula=stuff(@formula,charindex(char(13)+char(13),@formula),2,char(13))      
end    
while charindex(char(13)+' '+char(13),@formula)>0      
begin        
select @formula=stuff(@formula,charindex(char(13)+' '+char(13),@formula),3,char(13))      
end
select @addrlen=datalength (@formula)
while substring (@formula, @addrlen, 1)=char(13)
          select @formula=substring(@formula, 1, @addrlen-1)
select @full_address=@formula
if datalength(@full_address)>1
begin
update Name_Address set FULL_ADDRESS=@full_address,ADDRESS_FORMAT=@code where ADDRESS_NUM=@addrnum
     if @Preferred_Mail = 1
        begin
              update Name set FULL_ADDRESS = @full_address where ID = @ID
       end
end
end
END
fetch next from Get_Addrs into @ID, @Preferred_Mail,@addrnum,@ADDRESS_1,@ADDRESS_2,@ADDRESS_3,@CITY,@STATE_PROVINCE,@ZIP,@COUNTRY
END
close Get_Addrs
deallocate Get_Addrs

GO
GRANT EXECUTE ON  [dbo].[sp_asi_AddressUpdate] TO [IMIS]
GO